package dbgroup02.dao.db2;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import dbgroup02.bean.Passenger;
import dbgroup02.dao.PassengerDao;

public class PassengerDB2Dao implements PassengerDao {

	private JdbcTemplate jdbcTemplate;

	ParameterizedRowMapper<Passenger> getPassengerMapper = new ParameterizedRowMapper<Passenger>() {
		@Override
		public Passenger mapRow(ResultSet resultSet, int arg1)
				throws SQLException {
			Passenger passenger = new Passenger();
			passenger.setIdPassenger(resultSet.getInt(1));
			passenger.setFirstName(resultSet.getString(2));
			passenger.setLastName(resultSet.getString(3));
			passenger.setBirthDate(resultSet.getDate(4));
			passenger.setPassport(resultSet.getString(5));
			passenger.seteMail(resultSet.getString(6));
			passenger.setPhone(resultSet.getString(7));
			passenger.setIdAddress(resultSet.getInt(8));
			return passenger;
		}
	};

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public void addPassenger(Passenger passanger) {
		// We will have predefined Passengers, we don't need this method
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public Passenger getPassenger(int idPassenger) {
		List<Passenger> result = jdbcTemplate.query("SELECT * FROM "
				+ PassengerDao.SCHEMA + ".PASSANGER WHERE PASSANGER_ID = ?",
				new Object[] { idPassenger }, getPassengerMapper);

		if (result.size() > 0)
			return result.get(0);
		else
			return null;
	}

	@Override
	@Transactional(readOnly = true, propagation = Propagation.REQUIRED)
	public Passenger getPassenger(String lastName, int idTicket) {
		List<Passenger> result = jdbcTemplate
				.query("SELECT * FROM "
						+ PassengerDao.SCHEMA
						+ ".PASSANGER P LEFT OUTER JOIN TICKET T ON T.PASSANGER_ID = P.PASSANGER_ID WHERE P.LAST_NAME = ? AND T.TICKET_ID = ?",
						new Object[] { lastName, idTicket }, getPassengerMapper);

		if (result.size() > 0)
			return result.get(0);
		else
			return null;
	}

}
